package com.lanf.generator;

import com.lanf.system.exception.LanfException;

import java.sql.*;
import java.util.*;

/**
 * @author tanlingfei
 * @version 1.0
 * @description TODO
 * @date 2023-05-30 23:22:21
 */
public class DbUtil {
    //数据库URL和账号密码
    private String connectionURL = "jdbc:mysql://localhost:3306/base-lf?characterEncoding=utf-8&useSSL=false";
    private String username = "root";
    private String password = "root";
    private String driverName = "com.mysql.cj.jdbc.Driver";

    public static DbUtil n() {
        return new DbUtil();
    }

    public static DbUtil n(String connectionURL, String username, String password, String driverName) {
        return new DbUtil(connectionURL, username, password, driverName);
    }

    public DbUtil() {
    }

    public DbUtil(String connectionURL, String username, String password, String driverName) {
        this.connectionURL = connectionURL;
        this.username = username;
        this.password = password;
        this.driverName = driverName;
    }

    //数据库连接
    public Connection getConnection() {
        try {
            Class.forName(driverName);
            return DriverManager.getConnection(connectionURL, username, password);
        } catch (Exception e) {
            System.out.println("数据库连接失败");
            throw new LanfException(5240, "数据库连接失败");
        }
    }

    public List<Map<String, Object>> queryList(String sql) {
        Connection connection = this.getConnection();
        Statement sta = null;
        ResultSet res = null;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            sta = connection.createStatement();
            res = sta.executeQuery(sql);
            // 获取结果集结构（元素据）
            ResultSetMetaData rmd = res.getMetaData();
            // 获取字段数（即每条记录有多少个字段）
            int columnCount = rmd.getColumnCount();
            while (res.next()) {
                // 保存记录中的每个<字段名-字段值>
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; ++i) {
                    // <字段名-字段值>
                    rowData.put(rmd.getColumnName(i), res.getObject(i));
                }
                // 获取到了一条记录，放入list
                list.add(rowData);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (res != null)
                    res.close();
                if (sta != null)
                    sta.close();
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public Map<String, Object> getTableInfo(String name, String genType) throws Exception {
        Connection connection = this.getConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        // 获取所有表
        ResultSet tableResultSet = metaData.getTables(null, null, name, new String[]{"TABLE"});
        Map<String, Object> map = new Hashtable<String, Object>();
        ResultSet columnResultSet = null;
        try {
            if (tableResultSet.next()) {
                String tableName = tableResultSet.getString("TABLE_NAME");
                String modelName = Utils.toFirstUpper(Utils.lineToHump(tableName));
                String tableRemark = tableResultSet.getString("REMARKS");
                map.put("tableName", tableName);
                map.put("modelName", modelName);
                map.put("modelName2", Utils.toFirstLower(modelName));
                map.put("tableRemark", tableRemark);
                System.out.println("table:" + tableName + ",remarks:" + tableRemark);
                // 获取表字段结构
                columnResultSet = metaData.getColumns(null, null, tableName, "%");
                List<TableVo> list = new ArrayList<TableVo>();
                List<TableVo> requireList = new ArrayList<TableVo>();
                List<String> checkList = new ArrayList<>();
                while (columnResultSet.next()) {
                    // 字段名称
                    String columnName = columnResultSet.getString("COLUMN_NAME");
                    String attrName = Utils.lineToHump(columnName);
                    // 数据类型
                    String columnType = columnResultSet.getString("TYPE_NAME");
                    String dataType = columnType.toLowerCase();
                    //转换为java数据类型
                    columnType = Utils.dataToProperty(columnType);
                    // 字段长度
                    int datasize = columnResultSet.getInt("COLUMN_SIZE");
                    // 小数部分位数
                    int digits = columnResultSet.getInt("DECIMAL_DIGITS");
                    // 是否可为空 1代表可空 0代表不可为空
                    int nullable = columnResultSet.getInt("NULLABLE");
                    // 描述
                    String remarks = columnResultSet.getString("REMARKS");
                    TableVo tableVo = new TableVo(columnName, attrName, columnType, dataType, datasize, digits, nullable, remarks);
                    if ("id".equals(tableVo.getAttrName())) {
                        tableVo.setSort(100);
                    }
                    if (!"version".equals(tableVo.getColumeName())) {
                        if (!"1".equals(genType) || (!"treePath".equals(attrName) && !"level".equals(attrName))) {
                            list.add(tableVo);
                        }
                    }
                    checkList.add(attrName);
                    Collections.sort(list, new Comparator<TableVo>() {
                        @Override
                        public int compare(TableVo o1, TableVo o2) {
                            return o1.getSort() - o2.getSort();
                        }
                    });
                    if (nullable == 0 && !"id".equals(attrName) && !"isDeleted".equals(attrName)
                            && !"createTime".equals(attrName) && !"updateTime".equals(attrName)
                            && !"version".equals(attrName)) {
                        if (!"1".equals(genType) || (!"treePath".equals(attrName) && !"level".equals(attrName))) {
                            requireList.add(tableVo);
                        }
                    }
                    System.out.println(columnName + " " + columnType + " " + datasize + " " + digits + " " + nullable + " " + remarks);
                }
                StringBuilder err = new StringBuilder();
                if (!checkList.contains("id")) {
                    err.append("表(" + name + ")必须添加id(varchar)字段;");
                }
                if (!checkList.contains("isDeleted")) {
                    err.append("表(" + name + ")必须添加is_deleted(tinyint类型)字段");
                }
                if (!checkList.contains("createTime")) {
                    err.append("表(" + name + ")必须添加create_time(timestamp类型)字段");
                }
                if (!checkList.contains("updateTime")) {
                    err.append("表(" + name + ")必须添加update_time(timestamp类型)字段");
                }
                if (!checkList.contains("version")) {
                    err.append("表(" + name + ")必须添加version(bigint类型)字段");
                }
                if ("1".equals(genType)) { //树形列表判断必须添加的字段
                    if (!checkList.contains("name")) {
                        err.append("表(" + name + ")必须添加name(varchar类型)字段");
                    }
                    if (!checkList.contains("sortValue")) {
                        err.append("表(" + name + ")必须添加sort_value(int类型)字段");
                    }
                    if (!checkList.contains("parentId")) {
                        err.append("表(" + name + ")必须添加parent_id(varchar类型)字段");
                    }
                    if (!checkList.contains("treePath")) {
                        err.append("表(" + name + ")必须添加tree_path(varchar类型)字段");
                    }
                    if (!checkList.contains("level")) {
                        err.append("表(" + name + ")必须添加level(tinyint类型)字段");
                    }
                }
                if (err.length() > 0) {
                    throw new LanfException(5240, err.toString());
                }
                map.put("data", list);
                map.put("requireList", requireList);
            } else {
                throw new LanfException(5240, "表(" + name + ")在数据库中不存在");
            }
            return map;
        } finally {
            if (columnResultSet != null)
                columnResultSet.close();
            if (tableResultSet != null)
                tableResultSet.close();
            if (connection != null)
                connection.close();
        }
    }
}
